package com.xmy.aboutdb;

import com.xmy.aboutdb.bean.User;
import com.xmy.jdbcutils.DBConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
// 数据库增删查改

public class Demo2 {

    public static void main(String[] args) throws SQLException {

        // 查
        //query();

        // 增
        //insert();

        // 添加并返回主键值
       /* long primary = insertWithPK();
        System.out.println(primary);*/

        // 事务回滚
//        updateWithTran();

        // 批量添加 Batch
        List<User> users = new ArrayList<>();
        users.add(new User("LiMing", "Li", "IamLiMing"));
        users.add(new User("Yukoyi", "Yuki", "IamYuki"));
        insertWithBatch(users);

        // 改
        //update();

        // 删
        //delete();
    }

    // 查询
    public static void query() {

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBConnection.getConnection();
            String sql = "SELECT * FROM USER";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {

                StringBuilder message = new StringBuilder();

                message.append(rs.getInt("id"));
                message.append("-");
                message.append(rs.getString("username"));
                message.append("-");
                message.append(rs.getString("nickname"));
                message.append("-");
                message.append(rs.getString("password"));

                System.out.println(message.toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            try {
                DBConnection.release(rs, ps, con);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 增加
    public static void insert() {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            String sql = "INSERT INTO user (username, nickname, password) VALUES (?, ?, ?)";
            con = DBConnection.getConnection();
            ps = con.prepareStatement(sql);
            ps.setObject(1, "Vivia");
            ps.setObject(2, "Via");
            ps.setObject(3, "654321");
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBConnection.release(null, ps, con);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    // 插入记录并获取该记录的主键
    public static long insertWithPK() throws SQLException {
        try (Connection con = DBConnection.getConnection()) {
            try (PreparedStatement ps = con.prepareStatement("INSERT INTO user (username, nickname, password) VALUES (?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) {
                ps.setObject(1, "Bingo");
                ps.setObject(2, "Bin");
                ps.setObject(3, "098765");
                ps.executeUpdate();

                try (ResultSet rs = ps.getGeneratedKeys()) {
                    if (rs.next()) {
                        return rs.getLong(1);
                    }
                }
            }
        }
        return 0;
    }

    // 更改
    public static void update() throws SQLException {
        try (Connection con = DBConnection.getConnection()) {
            try (PreparedStatement ps = con.prepareStatement("UPDATE user SET nickname = ? WHERE username = ?")) {
                ps.setObject(1, "Col");
                ps.setObject(2, "Colorful");
                ps.executeUpdate();
            }

        }
    }

    // 删除
    public static void delete() throws SQLException {
        try (Connection con = DBConnection.getConnection()) {
            con.setAutoCommit(false);       // 事务自动提交默认为true，这里设置为 false
            try (PreparedStatement ps = con.prepareStatement("DELETE FROM user WHERE nickname = ?")) {
                ps.setObject(1, "Bin");
                ps.executeUpdate();
            }
        }
    }

    // 事务 Transaction
    public static void updateWithTran() throws SQLException {
        String un = "Tony Stark";
        String nn = "Tony";
        String pw = "BestManOfWorld";
        try (Connection con = DBConnection.getConnection()) {
            boolean isAutoCommit = con.getAutoCommit();
            con.setAutoCommit(false);
            try (PreparedStatement ps =
                         con.prepareStatement("INSERT INTO user (username, nickname, password) VALUES (?, ?, ?)")) {
                ps.setObject(1, un);
                ps.setObject(2, nn);
                ps.setObject(3, pw);
                int n = ps.executeUpdate();
                System.out.println(n + " inserted");
            }

            if (pw.equals("BestManOfWorld")) {
                con.rollback();
                System.out.println("sorry for the commit, rollback is executed");

            } else {
                con.commit();
                System.out.println("commit successfully");
            }

            con.setAutoCommit(isAutoCommit);
        }
    }

    // 批量操作 Batch
    public static void insertWithBatch(List<User> list) throws SQLException {
        try (Connection con = DBConnection.getConnection()) {
            try (PreparedStatement ps = con.prepareStatement("INSERT INTO " +
                    "user (username, nickname, password) VALUES (?, ?, ?)")) {

                for (User user :
                        list) {
                    ps.setObject(1, user.getUsername());
                    ps.setObject(2, user.getNickname());
                    ps.setObject(3, user.getPassword());
                    ps.addBatch();
                }

                int[] results = ps.executeBatch();
                for (int i = 0; i < results.length; i++) {
                    System.out.println(results[i] + " inserted");
                }
            }
        }

    }

}
